package ustc.xiaoyang.mapper;

import org.apache.ibatis.annotations.*;
import ustc.xiaoyang.pojo.Student;
import ustc.xiaoyang.pojo.dto.StudentPageQueryDTO;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Mapper
public interface StudentMapper {
    /**
     * 根据班级ID查询学生数量
     * @param clazzId
     * @return
     */
    @Select("select count(*) from student where clazz_id = #{clazzId}")
    Integer countByClazzId(Integer clazzId);

    /**
     * 分页查询学生信息
     * @param studentPageQueryDTO
     * @return
     */
    List<Student> pageList(StudentPageQueryDTO studentPageQueryDTO);

    /**
     * 添加学生信息
     * @param student
     */
    @Insert("insert into student(name, no, gender, phone, id_card, is_college, address, degree, graduation_date, clazz_id, create_time, update_time) " +
            "values(#{name},#{no},#{gender},#{phone},#{idCard},#{isCollege},#{address},#{degree},#{graduationDate},#{clazzId},#{createTime},#{updateTime})")
    void add(Student student);

    /**
     * 根据ID查询学生信息
     * @param id
     * @return
     */
    @Select("select * from student where id = #{id}")
    Student getById(Integer id);

    /**
     * 修改学生信息
     * @param student
     */
    void update(Student student);

    /**
     * 根据ID批量删除学生信息
     * @param ids
     */
    void deleteByIds(List<Integer> ids);

    /**
     * 添加学生违规信息
     * @param id
     * @param score
     * @param updateTime
     */
    @Update("update student set violation_count = violation_count + 1,violation_score = violation_score + #{score}, update_time = #{updateTime} " +
            "where id = #{id}")
    void updateViolation(Integer id, Integer score, LocalDateTime updateTime);

    /**
     * 查询学生班级人数统计
     * @return
     */
    @MapKey("clazzName")
    @Select("select c.name clazzName,count(*) total from student s left join clazz c on s.clazz_id = c.id group by c.name")
    List<Map<String,Object>> getStudentClazzData();

    @MapKey("name")
    List<Map<String, Object>> getStudentDegreeData();
}
